﻿using System;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.IO;
namespace Yunzuan.Foundation.Excel
{

    /// <summary>
    /// Excel表格操作
    /// </summary>
    public class ExcelHelper
    {
        #region 获取Excel数据
        /// <summary>
        /// 根据Excel物理路径、表名(Sheet名)获取数据集
        /// </summary>
        /// <param name="FileFullPath">excel文件完整路径</param>
        /// <param name="SheetName">工作簿名称</param>
        /// <returns></returns>
        public static DataTable GetExcelToDataTableBySheet(string FileFullPath, string SheetName, string fileType)
        {
            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'"; //此连接可以操作.xls与.xlsx文件,【HDR为YES表示首行为字段,为NO表示首行为数据】
            //string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";//旧版连接
            
            if (fileType == ".xls")
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileFullPath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            else
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FileFullPath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";


            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", SheetName), conn);//("select * from [Sheet1$]", conn);
            odda.Fill(ds);
            conn.Close();
            return ds.Tables[0];
        }


        /// <summary>
        /// 根据Excel物理路径获取Excel文件中所有表名
        /// </summary>
        /// <param name="FileFullPath">excel文件完整路径</param>
        /// <returns></returns>
        public static String[] GetExcelSheetNames(string FileFullPath)
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;

            try
            {

                string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'"; //此连接可以操作.xls与.xlsx文件
              

                objConn = new OleDbConnection(strConn);
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }
                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }

                return excelSheets;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
        #endregion

        /// <summary>
        /// 获取CSV导入的数据
        /// </summary>
        /// <param name="filePath">文件所在路径(不包含文件名)</param>
        /// <param name="fileName">文件名称</param>
        /// <returns></returns>
        public static DataTable GetCsvData(string filePath, string fileName)
        {
            string path = Path.Combine(filePath, fileName);
            string connString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + filePath + ";Extensions=asc,csv,tab,txt;";
            try
            {
                using (OdbcConnection odbcConn = new OdbcConnection(connString))
                {
                    odbcConn.Open();
                    OdbcCommand oleComm = new OdbcCommand();
                    oleComm.Connection = odbcConn;
                    oleComm.CommandText = "select * from [" + fileName + "]";
                    OdbcDataAdapter adapter = new OdbcDataAdapter(oleComm);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    odbcConn.Close();
                    return ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
